package com.example.chandao.common.easyexcel.export;

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

public class ExcelExport {

    // 定义表头相关信息，与图片中列对应
    private static final String[] HEADER_PRODUCT_NAME = {"产品名称"};
    private static final String[] HEADER_STORY = {"需求", "草稿", "激活中", "已关闭"};
    private static final String[] HEADER_TASK = {"任务", "未开始", "进行中", "已暂停", "已完成", "已关闭"};
    private static final String[] HEADER_BUG = {"BUG", "激活中", "已解决", "已关闭"};

    public static void main(String[] args) {
        // 模拟从接口获取的数据（这里就是你提供的 JSON 对应的 Map 结构，可根据实际情况替换为真实数据获取逻辑）
        Map<String, Object> dataMap = getDataMap();

        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("产品数据统计");

        // 创建单元格样式
        CellStyle headerStyle = createHeaderStyle(workbook);
        CellStyle contentStyle = createContentStyle(workbook);

        // 合并单元格并填充表头
        int rowIndex = 0;
        rowIndex = fillHeader(sheet, headerStyle, rowIndex);

        // 填充数据行
        rowIndex = fillData(sheet, contentStyle, dataMap, rowIndex);

        // 自动调整列宽
        autoSizeColumns(sheet);

        // 导出 Excel 到文件
        try (FileOutputStream fos = new FileOutputStream("product_data.xlsx")) {
            workbook.write(fos);
            System.out.println("Excel 导出成功！");
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private static Map<String, Object> getDataMap() {
        Map<String, Object> dataMap = new LinkedHashMap<>();

        // 添加 "06 MERP-医学伦理审查自动化信息平台" 产品数据
        Map<String, Object> product1 = new LinkedHashMap<>();
        product1.put("task", Arrays.asList(
                createItem(143, "06 MERP-医学伦理审查自动化信息平台", 2, "task", "closed"),
                createItem(143, "06 MERP-医学伦理审查自动化信息平台", 5, "task", "done")
        ));
        product1.put("story", Arrays.asList(
                createItem(143, "06 MERP-医学伦理审查自动化信息平台", 3, "story", "active"),
                createItem(143, "06 MERP-医学伦理审查自动化信息平台", 2, "story", "closed"),
                createItem(143, "06 MERP-医学伦理审查自动化信息平台", 6, "story", "draft")
        ));
        dataMap.put("06 MERP-医学伦理审查自动化信息平台", product1);

        // 添加 "07 里恩eLearning-在线培训系统" 产品数据
        Map<String, Object> product2 = new LinkedHashMap<>();
        product2.put("story", Collections.singletonList(
                createItem(42, "07 里恩eLearning-在线培训系统", 12, "story", "draft")
        ));
        dataMap.put("07 里恩eLearning-在线培训系统", product2);

        // 添加 "05 SCTMS系统-西安大兴医院" 产品数据
        Map<String, Object> product3 = new LinkedHashMap<>();
        product3.put("bug", Collections.singletonList(
                createItem(259, "05 SCTMS系统-西安大兴医院", 1, "bug", "active")
        ));
        product3.put("story", Arrays.asList(
                createItem(259, "05 SCTMS系统-西安大兴医院", 6, "story", "active"),
                createItem(259, "05 SCTMS系统-西安大兴医院", 10, "story", "draft")
        ));
        dataMap.put("05 SCTMS系统-西安大兴医院", product3);

        // 添加 "01 EDC-里恩临床试验电子数据采集系统" 产品数据
        Map<String, Object> product4 = new LinkedHashMap<>();
        product4.put("task", Arrays.asList(
                createItem(5, "01 EDC-里恩临床试验电子数据采集系统", 1, "task", "closed"),
                createItem(5, "01 EDC-里恩临床试验电子数据采集系统", 10, "task", "doing"),
                createItem(5, "01 EDC-里恩临床试验电子数据采集系统", 10, "task", "done"),
                createItem(5, "01 EDC-里恩临床试验电子数据采集系统", 2, "task", "pause"),
                createItem(5, "01 EDC-里恩临床试验电子数据采集系统", 344, "task", "wait")
        ));
        product4.put("bug", Arrays.asList(
                createItem(5, "01 EDC-里恩临床试验电子数据采集系统", 5, "bug", "active"),
                createItem(5, "01 EDC-里恩临床试验电子数据采集系统", 12, "bug", "closed"),
                createItem(5, "01 EDC-里恩临床试验电子数据采集系统", 4, "bug", "resolved")
        ));
        product4.put("story", Arrays.asList(
                createItem(5, "01 EDC-里恩临床试验电子数据采集系统", 4, "story", "active"),
                createItem(5, "01 EDC-里恩临床试验电子数据采集系统", 2, "story", "closed"),
                createItem(5, "01 EDC-里恩临床试验电子数据采集系统", 19, "story", "draft")
        ));
        dataMap.put("01 EDC-里恩临床试验电子数据采集系统", product4);

        // 添加 "05 SCTMS系统-延安大学咸阳医院" 产品数据
        Map<String, Object> product5 = new LinkedHashMap<>();
        product5.put("bug", Arrays.asList(
                createItem(453, "05 SCTMS系统-延安大学咸阳医院", 9, "bug", "closed"),
                createItem(453, "05 SCTMS系统-延安大学咸阳医院", 1, "bug", "resolved")
        ));
        product5.put("story", Arrays.asList(
                createItem(453, "05 SCTMS系统-延安大学咸阳医院", 6, "story", "closed"),
                createItem(453, "05 SCTMS系统-延安大学咸阳医院", 2, "story", "draft")
        ));
        dataMap.put("05 SCTMS系统-延安大学咸阳医院", product5);

        // 添加 "03 PV-药物警戒系统" 产品数据
        Map<String, Object> product6 = new LinkedHashMap<>();
        product6.put("task", Collections.singletonList(
                createItem(151, "03 PV-药物警戒系统", 5, "task", "closed")
        ));
        product6.put("bug", Arrays.asList(
                createItem(151, "03 PV-药物警戒系统", 16, "bug", "active"),
                createItem(151, "03 PV-药物警戒系统", 38, "bug", "closed")
        ));
        product6.put("story", Collections.singletonList(
                createItem(151, "03 PV-药物警戒系统", 26, "story", "draft")
        ));
        dataMap.put("03 PV-药物警戒系统", product6);

        // 添加 "02 MagMinDA 3.0" 产品数据
        Map<String, Object> product7 = new LinkedHashMap<>();
        product7.put("task", Collections.singletonList(
                createItem(431, "02 MagMinDA 3.0", 1, "task", "doing")
        ));
        product7.put("bug", Arrays.asList(
                createItem(431, "02 MagMinDA 3.0", 3, "bug", "active"),
                createItem(431, "02 MagMinDA 3.0", 14, "bug", "closed"),
                createItem(431, "02 MagMinDA 3.0", 4, "bug", "resolved")
        ));
        product7.put("story", Arrays.asList(
                createItem(431, "02 MagMinDA 3.0", 9, "story", "closed"),
                createItem(431, "02 MagMinDA 3.0", 20, "story", "draft")
        ));
        dataMap.put("02 MagMinDA 3.0", product7);

        // 添加 "05 SCTMS系统-陕西省人民医院" 产品数据
        Map<String, Object> product8 = new LinkedHashMap<>();
        product8.put("task", Collections.singletonList(
                createItem(452, "05 SCTMS系统-陕西省人民医院", 1, "task", "done")
        ));
        product8.put("bug", Collections.singletonList(
                createItem(452, "05 SCTMS系统-陕西省人民医院", 2, "bug", "resolved")
        ));
        product8.put("story", Collections.singletonList(
                createItem(452, "05 SCTMS系统-陕西省人民医院", 3, "story", "draft")
        ));
        dataMap.put("05 SCTMS系统-陕西省人民医院", product8);

        // 添加 "09 ePro 电子患者报告临床结局系统" 产品数据
        Map<String, Object> product9 = new LinkedHashMap<>();
        product9.put("task", Collections.singletonList(
                createItem(286, "09 ePro 电子患者报告临床结局系统", 1, "task", "doing")
        ));
        product9.put("story", Collections.singletonList(
                createItem(286, "09 ePro 电子患者报告临床结局系统", 16, "story", "draft")
        ));
        dataMap.put("09 ePro 电子患者报告临床结局系统", product9);

        // 添加 "08 eTMF-里恩医学电子文档管理系统" 产品数据
        Map<String, Object> product10 = new LinkedHashMap<>();
        product10.put("task", Arrays.asList(
                createItem(152, "08 eTMF-里恩医学电子文档管理系统", 1, "task", "doing"),
                createItem(152, "08 eTMF-里恩医学电子文档管理系统", 1, "task", "done"),
                createItem(152, "08 eTMF-里恩医学电子文档管理系统", 7, "task", "wait")
        ));
        product10.put("story", Collections.singletonList(
                createItem(152, "08 eTMF-里恩医学电子文档管理系统", 17, "story", "draft")
        ));
        dataMap.put("08 eTMF-里恩医学电子文档管理系统", product10);

        // 添加 "02 MagMinDA临床试验随机分组&药物追踪管理系统(V2.0)" 产品数据
        Map<String, Object> product11 = new LinkedHashMap<>();
        product11.put("task", Arrays.asList(
                createItem(174, "02 MagMinDA临床试验随机分组&药物追踪管理系统(V2.0)", 1, "task", "cancel"),
                createItem(174, "02 MagMinDA临床试验随机分组&药物追踪管理系统(V2.0)", 2, "task", "doing"),
                createItem(174, "02 MagMinDA临床试验随机分组&药物追踪管理系统(V2.0)", 11, "task", "wait")
        ));
        product11.put("bug", Collections.singletonList(
                createItem(174, "02 MagMinDA临床试验随机分组&药物追踪管理系统(V2.0)", 2, "bug", "closed")
        ));
        product11.put("story", Collections.singletonList(
                createItem(174, "02 MagMinDA临床试验随机分组&药物追踪管理系统(V2.0)", 5, "story", "draft")
        ));
        dataMap.put("02 MagMinDA临床试验随机分组&药物追踪管理系统(V2.0)", product11);

        // 添加 "02 MagMinDA小程序 3.0" 产品数据
        Map<String, Object> product12 = new LinkedHashMap<>();
        product12.put("story", Collections.singletonList(
                createItem(458, "02 MagMinDA小程序 3.0", 11, "story", "draft")
        ));
        dataMap.put("02 MagMinDA小程序 3.0", product12);

        // 添加 "04 CTMS-临床研究管理系统" 产品数据
        Map<String, Object> product13 = new LinkedHashMap<>();
        product13.put("task", Arrays.asList(
                createItem(158, "04 CTMS-临床研究管理系统", 3, "task", "doing"),
                createItem(158, "04 CTMS-临床研究管理系统", 1, "task", "done"),
                createItem(158, "04 CTMS-临床研究管理系统", 2, "task", "pause"),
                createItem(158, "04 CTMS-临床研究管理系统", 1, "task", "wait")
        ));
        product13.put("bug", Collections.singletonList(
                createItem(158, "04 CTMS-临床研究管理系统", 2, "bug", "closed")
        ));
        product13.put("story", Collections.singletonList(
                createItem(158, "04 CTMS-临床研究管理系统", 52, "story", "draft")
        ));
        dataMap.put("04 CTMS-临床研究管理系统", product13);

        return dataMap;
    }

    // 辅助方法：创建任务、故事或缺陷项
    private static Map<String, Object> createItem(int productId, String productName, int count, String type, String status) {
        Map<String, Object> item = new LinkedHashMap<>();
        item.put("productId", productId);
        item.put("productName", productName);
        item.put("count", count);
        item.put("type", type);
        item.put("status", status);
        item.put("startTime", null);
        item.put("endTime", null);
        item.put("searchTime", null);
        item.put("createTime", null);
        item.put("reviewTime", null);
        item.put("closeTime", null);
        item.put("finishTime", null);
        item.put("resolveTime", null);
        return item;
    }

    private static int fillHeaders(Sheet sheet, CellStyle headerStyle, int rowIndex) {
        // 合并产品名称列
        mergeAndFillHeader(sheet, rowIndex, 0, 0, HEADER_PRODUCT_NAME, headerStyle);
        rowIndex++;
        // 合并需求列
        mergeAndFillHeader(sheet, rowIndex, 1, 3, HEADER_STORY, headerStyle);
        // 合并任务列
        mergeAndFillHeader(sheet, rowIndex, 4, 8, HEADER_TASK, headerStyle);
        // 合并 BUG 列
        mergeAndFillHeader(sheet, rowIndex, 9, 11, HEADER_BUG, headerStyle);
        rowIndex++;
        return rowIndex;
    }

    private static int fillHeader(Sheet sheet, CellStyle headerStyle, int rowIndex) {
        // 第一行表头：产品名称、需求、任务、BUG 标题
        Row row1 = sheet.createRow(rowIndex);

        // 设置产品名称单元格 (A1)
        Cell productNameCell = row1.createCell(0);
        productNameCell.setCellValue(HEADER_PRODUCT_NAME[0]);
        productNameCell.setCellStyle(headerStyle);

        // 合并需求区域 (B1-D1)
        sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 3));
        Cell storyHeaderCell = row1.createCell(1);
        storyHeaderCell.setCellValue(HEADER_STORY[0]);
        storyHeaderCell.setCellStyle(headerStyle);

        // 合并任务区域 (E1-I1)
        sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 4, 8));
        Cell taskHeaderCell = row1.createCell(4);
        taskHeaderCell.setCellValue(HEADER_TASK[0]);
        taskHeaderCell.setCellStyle(headerStyle);

        // 合并BUG区域 (J1-L1)
        sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 9, 11));
        Cell bugHeaderCell = row1.createCell(9);
        bugHeaderCell.setCellValue(HEADER_BUG[0]);
        bugHeaderCell.setCellStyle(headerStyle);

        rowIndex++;

        // 第二行表头：需求、任务、BUG 的子类别
        Row row2 = sheet.createRow(rowIndex);

        // 需求子类别 (B2-D2)
        for (int i = 0; i < HEADER_STORY.length - 1; i++) {
            Cell cell = row2.createCell(i + 1);
            cell.setCellValue(HEADER_STORY[i + 1]);
            cell.setCellStyle(headerStyle);
        }

        // 任务子类别 (E2-I2)
        for (int i = 0; i < HEADER_TASK.length - 1; i++) {
            Cell cell = row2.createCell(i + 4);
            cell.setCellValue(HEADER_TASK[i + 1]);
            cell.setCellStyle(headerStyle);
        }

        // BUG子类别 (J2-L2)
        for (int i = 0; i < HEADER_BUG.length - 1; i++) {
            Cell cell = row2.createCell(i + 9);
            cell.setCellValue(HEADER_BUG[i + 1]);
            cell.setCellStyle(headerStyle);
        }

        rowIndex++;
        return rowIndex;
    }


    private static void mergeAndFillHeader(Sheet sheet, int rowIndex, int startCol, int endCol, String[] headerTexts, CellStyle style) {
        Row row = sheet.createRow(rowIndex);
        // 合并单元格
        sheet.addMergedRegion(new CellRangeAddress(rowIndex, rowIndex, startCol, endCol));
        Cell cell = row.createCell(startCol);
        cell.setCellValue(headerTexts[0]);
        cell.setCellStyle(style);

        if (headerTexts.length > 1) {
            for (int i = 1; i < headerTexts.length; i++) {
                cell = row.createCell(startCol + i);
                cell.setCellValue(headerTexts[i]);
                cell.setCellStyle(style);
            }
        }
    }

    private static int fillData(Sheet sheet, CellStyle style, Map<String, Object> dataMap, int rowIndex) {
        for (Map.Entry<String, Object> entry : dataMap.entrySet()) {
            String productName = entry.getKey();
            Map<String, Object> productData = (Map<String, Object>) entry.getValue();

            Row row = sheet.createRow(rowIndex);
            Cell productCell = row.createCell(0);
            productCell.setCellValue(productName);
            productCell.setCellStyle(style);

            // 处理需求（story）数据
            List<Map<String, Object>> storyList = (List<Map<String, Object>>) productData.get("story");
            fillStoryData(row, style, storyList);

            // 处理任务（task）数据
            List<Map<String, Object>> taskList = (List<Map<String, Object>>) productData.get("task");
            fillTaskData(row, style, taskList);

            // 处理 BUG 数据
            List<Map<String, Object>> bugList = (List<Map<String, Object>>) productData.get("bug");
            fillBugData(row, style, bugList);

            rowIndex++;
        }
        return rowIndex;
    }

    private static void fillStoryData(Row row, CellStyle style, List<Map<String, Object>> storyList) {
        int colIndex = 1;
        Map<String, Integer> storyStatusCount = new LinkedHashMap<>();
        storyStatusCount.put("draft", 0);
        storyStatusCount.put("active", 0);
        storyStatusCount.put("closed", 0);
        if (storyList != null) {
            for (Map<String, Object> story : storyList) {
                String status = (String) story.get("status");
                int count = (int) story.get("count");
                storyStatusCount.put(status, storyStatusCount.get(status) + count);
            }
        }
        row.createCell(colIndex++).setCellValue(storyStatusCount.get("draft"));
        row.getCell(colIndex - 1).setCellStyle(style);
        row.createCell(colIndex++).setCellValue(storyStatusCount.get("active"));
        row.getCell(colIndex - 1).setCellStyle(style);
        row.createCell(colIndex++).setCellValue(storyStatusCount.get("closed"));
        row.getCell(colIndex - 1).setCellStyle(style);
    }

    private static void fillTaskData(Row row, CellStyle style, List<Map<String, Object>> taskList) {
        int colIndex = 4;
        Map<String, Integer> taskStatusCount = new LinkedHashMap<>();
        taskStatusCount.put("wait", 0);
        taskStatusCount.put("doing", 0);
        taskStatusCount.put("pause", 0);
        taskStatusCount.put("done", 0);
        taskStatusCount.put("closed", 0);
        if (taskList != null) {
            for (Map<String, Object> task : taskList) {
                String status = (String) task.get("status");
                int count = (int) task.get("count");
                taskStatusCount.put(status, taskStatusCount.get(status) + count);
            }
        }
        row.createCell(colIndex++).setCellValue(taskStatusCount.get("wait"));
        row.getCell(colIndex - 1).setCellStyle(style);
        row.createCell(colIndex++).setCellValue(taskStatusCount.get("doing"));
        row.getCell(colIndex - 1).setCellStyle(style);
        row.createCell(colIndex++).setCellValue(taskStatusCount.get("pause"));
        row.getCell(colIndex - 1).setCellStyle(style);
        row.createCell(colIndex++).setCellValue(taskStatusCount.get("done"));
        row.getCell(colIndex - 1).setCellStyle(style);
        row.createCell(colIndex++).setCellValue(taskStatusCount.get("closed"));
        row.getCell(colIndex - 1).setCellStyle(style);
    }

    private static void fillBugData(Row row, CellStyle style, List<Map<String, Object>> bugList) {
        int colIndex = 9;
        Map<String, Integer> bugStatusCount = new LinkedHashMap<>();
        bugStatusCount.put("active", 0);
        bugStatusCount.put("resolved", 0);
        bugStatusCount.put("closed", 0);
        if (bugList != null) {
            for (Map<String, Object> bug : bugList) {
                String status = (String) bug.get("status");
                int count = (int) bug.get("count");
                bugStatusCount.put(status, bugStatusCount.get(status) + count);
            }
        }
        row.createCell(colIndex++).setCellValue(bugStatusCount.get("active"));
        row.getCell(colIndex - 1).setCellStyle(style);
        row.createCell(colIndex++).setCellValue(bugStatusCount.get("resolved"));
        row.getCell(colIndex - 1).setCellStyle(style);
        row.createCell(colIndex++).setCellValue(bugStatusCount.get("closed"));
        row.getCell(colIndex - 1).setCellStyle(style);
    }

    private static CellStyle createHeaderStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 12);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        style.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        return style;
    }

    private static CellStyle createContentStyle(Workbook workbook) {
        CellStyle style = workbook.createCellStyle();
        Font font = workbook.createFont();
        font.setFontHeightInPoints((short) 11);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);
        style.setVerticalAlignment(VerticalAlignment.CENTER);
        style.setBorderTop(BorderStyle.THIN);
        style.setBorderBottom(BorderStyle.THIN);
        style.setBorderLeft(BorderStyle.THIN);
        style.setBorderRight(BorderStyle.THIN);
        return style;
    }

    private static void autoSizeColumns(Sheet sheet) {
        for (int i = 0; i <= 11; i++) {
            sheet.autoSizeColumn(i);
        }
    }
}